﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;

using HsTec.Mobile.Green.Util;


namespace HsTec.Mobile.Green.DAL
{
    /// <summary>
    /// 数据访问类ReportTemplate。
    /// </summary>
    public class ReportTemplateDAL
    {
        public ReportTemplateDAL()
        { }
        #region  成员方法
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string sExistsWhere)
        {
            return SQLHelper.Exists("tb_ReportTemplate", sExistsWhere);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(HsTec.Mobile.Green.Model.ReportTemplate model)
        {
            SQLHelper.myconn.Open();
            SqlCommand command = SQLHelper.myconn.CreateCommand();
            SqlTransaction transaction;
            transaction = SQLHelper.myconn.BeginTransaction();
            command.Connection = SQLHelper.myconn;
            command.Transaction = transaction;
            try
            {
                //0.判断节点是否已经存在
                string sExist = SQLHelper.ExecuteString("select count(RT_ID) from tb_ReportTemplate where FieldName='" + model.FieldName + "' and FieldParent ='" + model.FieldParent + "'", SQLHelper.myconn, transaction);
                if (int.Parse(sExist) > 0)
                    return 2;
                //1.插入节点数据
                StringBuilder strInsert = new StringBuilder();
                strInsert.Append("insert into [tb_ReportTemplate](");
                strInsert.Append("FieldName,FieldAlias,FieldType,FieldSelect,SelectContent,EecelCell,Remark,FieldParent,FieldChildCount,FieldNo,FieldFormula,FieldTemplate");
                strInsert.Append(")");
                strInsert.Append(" values (");
                strInsert.Append("'" + model.FieldName + "',");
                strInsert.Append("'" + model.FieldAlias + "',");
                strInsert.Append("" + model.FieldType + ",");
                strInsert.Append("" + model.FieldSelect + ",");
                strInsert.Append("'" + model.SelectContent + "',");
                strInsert.Append("'" + model.EecelCell + "',");
                strInsert.Append("'" + model.Remark + "',");
                strInsert.Append("" + model.FieldParent + ",");
                strInsert.Append("" + model.FieldChildCount + ",");
                strInsert.Append("" + model.FieldNo + ",");
                strInsert.Append("'" + model.FieldFormula + "',");
                strInsert.Append("" + model.FieldTemplate + "");
                strInsert.Append(")");
                command.CommandText = strInsert.ToString();
                command.ExecuteNonQuery();
                //2.修改父节点中的节点类型为父节点
                StringBuilder strUpdate = new StringBuilder();
                strUpdate.Append("update tb_ReportTemplate set ");
                strUpdate.Append("FieldChildCount=FieldChildCount+1");
                strUpdate.Append(" where RT_ID=" + model.FieldParent + " ");
                command.CommandText = strUpdate.ToString();
                command.ExecuteNonQuery();
                //3.修改表结构
                //3.1 查找表是否存在
                //string parent = GetTemplateName(model.FieldParent, SQLHelper.myconn, transaction); 
                //---------------------------------------------------------------------------------------------------
                // 该处需要改进 怎么能快速的判断出节点所对应的表
                //---------------------------------------------------------------------------------------------------
                string parent = SQLHelper.ExecuteString("select FieldName from tb_ReportTemplate where RT_ID=" + model.FieldTemplate, SQLHelper.myconn, transaction);
                string tbExist = SQLHelper.ExecuteString("SELECT ISNULL(OBJECTPROPERTY(OBJECT_ID('rt_" + parent + "'),   'IsUserTable'),   0)", SQLHelper.myconn, transaction);
                //3.2 如果存在 添加字段到表中
                if (tbExist == "1")
                {
                    StringBuilder strAlert = new StringBuilder();
                    strAlert.Append("ALTER table [rt_" + parent + "] add [" + model.FieldName + "] [nvarchar](100) null ");
                    command.CommandText = strAlert.ToString();
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
                return 1;
            }
            catch
            {
                transaction.Rollback();
                return 0;
            }
            finally
            {
                transaction.Dispose();
                SQLHelper.myconn.Close();
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(HsTec.Mobile.Green.Model.ReportTemplate model)
        {
            SQLHelper.myconn.Open();
            SqlCommand command = SQLHelper.myconn.CreateCommand();
            SqlTransaction transaction;
            transaction = SQLHelper.myconn.BeginTransaction();
            command.Connection = SQLHelper.myconn;
            command.Transaction = transaction;
            try
            {
                //获取没修改前的字段名
                string tmpName = SQLHelper.ExecuteString("select FieldName from tb_ReportTemplate where RT_ID=" + model.RT_ID, SQLHelper.myconn, transaction);
                string sExist = SQLHelper.ExecuteString("select count(RT_ID) from tb_ReportTemplate where FieldName='" + model.FieldName + "' and FieldName!='" + tmpName + "' and FieldParent ='" + model.FieldParent + "'", SQLHelper.myconn, transaction);
                if (int.Parse(sExist) > 0)
                    return 2;
                //1.插入节点数据
                StringBuilder strSqlUpdate = new StringBuilder();
                strSqlUpdate.Append("update tb_ReportTemplate set ");
                strSqlUpdate.Append("FieldName='" + model.FieldName + "',");
                strSqlUpdate.Append("FieldAlias='" + model.FieldAlias + "',");
                strSqlUpdate.Append("FieldType=" + model.FieldType + ",");
                strSqlUpdate.Append("FieldSelect=" + model.FieldSelect + ",");
                strSqlUpdate.Append("SelectContent='" + model.SelectContent + "',");
                strSqlUpdate.Append("EecelCell='" + model.EecelCell + "',");
                strSqlUpdate.Append("Remark='" + model.Remark + "',");
                strSqlUpdate.Append("FieldParent=" + model.FieldParent + ",");
                strSqlUpdate.Append("FieldChildCount=" + model.FieldChildCount + ",");
                strSqlUpdate.Append("FieldNo=" + model.FieldNo + ",");
                strSqlUpdate.Append("FieldFormula='" + model.FieldFormula + "',");
                strSqlUpdate.Append("FieldTemplate=" + model.FieldTemplate + " ");
                strSqlUpdate.Append(" where RT_ID=" + model.RT_ID + " ");
                command.CommandText = strSqlUpdate.ToString();
                command.ExecuteNonQuery();
                //3.修改表结构
                //3.1 查找表是否存在
                //string parent = GetTemplateName(model.FieldParent, SQLHelper.myconn, transaction);
                string parent = SQLHelper.ExecuteString("select FieldName from tb_ReportTemplate where RT_ID=" + model.FieldTemplate, SQLHelper.myconn, transaction);
                string tbExist = SQLHelper.ExecuteString("SELECT ISNULL(OBJECTPROPERTY(OBJECT_ID('rt_" + parent + "'),   'IsUserTable'),   0)", SQLHelper.myconn, transaction);
                //3.2 如果存在 修改字段到表中
                if (tbExist == "1")
                {
                    if (model.FieldChildCount == 0)
                    {
                        StringBuilder strAlert = new StringBuilder();
                        strAlert.Append("EXEC   sp_rename   'rt_" + parent + "." + tmpName + "',  '" + model.FieldName + "',   'COLUMN ' ");
                        command.CommandText = strAlert.ToString();
                        command.ExecuteNonQuery();
                    }
                }
                transaction.Commit();
                return 1;
            }
            catch
            {
                transaction.Rollback();
                return 0;
            }
            finally
            {
                transaction.Dispose();
                SQLHelper.myconn.Close();
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int uid)
        {
            HsTec.Mobile.Green.Model.ReportTemplate rt = this.GetModel(uid);
            SQLHelper.myconn.Open();
            SqlCommand command = SQLHelper.myconn.CreateCommand();
            SqlTransaction transaction;
            transaction = SQLHelper.myconn.BeginTransaction();
            command.Connection = SQLHelper.myconn;
            command.Transaction = transaction;
            try
            {
                //0 修改父节点孩子的个数
                command.CommandText ="update tb_ReportTemplate set FieldChildCount = FieldChildCount -1 where RT_ID=" + rt.FieldParent;
                command.ExecuteNonQuery();
                //1 删除节点和它的所有子节点
                List<string> list = new List<string>();
                if (rt.FieldChildCount == 0)
                    list.Add(rt.FieldName);
                string sDelList = GetChildID(uid, list,transaction);
                sDelList += uid.ToString();
                command.CommandText = "delete from tb_ReportTemplate where RT_ID in ( " + sDelList + " )";
                command.ExecuteNonQuery();
                //2.处理数据库中的字段
                string parent = SQLHelper.ExecuteString("select FieldName from tb_ReportTemplate where RT_ID=" + rt.FieldTemplate, SQLHelper.myconn, transaction);
                string tbExist = SQLHelper.ExecuteString("SELECT ISNULL(OBJECTPROPERTY(OBJECT_ID('rt_" + parent + "'),   'IsUserTable'),   0)", SQLHelper.myconn, transaction);
                if (tbExist == "1")
                {
                    //判断是表还是字段
                    if (rt.FieldTemplate == 0)
                    {
                        command.CommandText = "drop table rt_" + rt.FieldName;
                        command.ExecuteNonQuery();
                    }
                    else
                    {
                        foreach(string tmp in list)
                        {
                            command.CommandText = "ALTER TABLE [rt_" + parent + "] DROP COLUMN [" + tmp + "]";
                            command.ExecuteNonQuery();
                        }
                    }
                }
                transaction.Commit();
                return 1;
            }
            catch
            {
                transaction.Rollback();
                return 0;
            }
            finally
            {
                transaction.Dispose();
                SQLHelper.myconn.Close();
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public HsTec.Mobile.Green.Model.ReportTemplate GetModel(int RT_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 RT_ID,FieldName,FieldAlias,FieldType,FieldSelect,SelectContent,EecelCell,Remark,FieldParent,FieldChildCount,FieldNo,FieldFormula,FieldTemplate ");
            strSql.Append(" FROM tb_ReportTemplate ");
            strSql.Append(" where RT_ID=@RT_ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@RT_ID", SqlDbType.Int,4)};
            parameters[0].Value = RT_ID;

            HsTec.Mobile.Green.Model.ReportTemplate model = new HsTec.Mobile.Green.Model.ReportTemplate();
            DataSet ds = SQLHelper.ExecuteDataset(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["RT_ID"].ToString() != "")
                {
                    model.RT_ID = int.Parse(ds.Tables[0].Rows[0]["RT_ID"].ToString());
                }
                model.FieldName = ds.Tables[0].Rows[0]["FieldName"].ToString();
                model.FieldAlias = ds.Tables[0].Rows[0]["FieldAlias"].ToString();
                if (ds.Tables[0].Rows[0]["FieldType"].ToString() != "")
                {
                    model.FieldType = int.Parse(ds.Tables[0].Rows[0]["FieldType"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FieldSelect"].ToString() != "")
                {
                    model.FieldSelect = int.Parse(ds.Tables[0].Rows[0]["FieldSelect"].ToString());
                }
                model.SelectContent = ds.Tables[0].Rows[0]["SelectContent"].ToString();
                model.EecelCell = ds.Tables[0].Rows[0]["EecelCell"].ToString();
                model.Remark = ds.Tables[0].Rows[0]["Remark"].ToString();
                if (ds.Tables[0].Rows[0]["FieldParent"].ToString() != "")
                {
                    model.FieldParent = int.Parse(ds.Tables[0].Rows[0]["FieldParent"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FieldChildCount"].ToString() != "")
                {
                    model.FieldChildCount = int.Parse(ds.Tables[0].Rows[0]["FieldChildCount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FieldNo"].ToString() != "")
                {
                    model.FieldNo = int.Parse(ds.Tables[0].Rows[0]["FieldNo"].ToString());
                }
                model.FieldFormula = ds.Tables[0].Rows[0]["FieldFormula"].ToString();
                if (ds.Tables[0].Rows[0]["FieldTemplate"].ToString() != "")
                {
                    model.FieldTemplate = int.Parse(ds.Tables[0].Rows[0]["FieldTemplate"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataView GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select RT_ID,FieldName,FieldAlias,FieldType,FieldSelect,SelectContent,EecelCell,Remark,FieldParent,FieldChildCount,FieldNo,FieldFormula,FieldTemplate ");
            strSql.Append(" FROM tb_ReportTemplate ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by FieldNo");
            return SQLHelper.ExecuteDataView(strSql.ToString());            
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataView GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" RT_ID,FieldName,FieldAlias,FieldType,FieldSelect,SelectContent,EecelCell,Remark,FieldParent,FieldChildCount,FieldNo,FieldFormula,FieldTemplate ");
            strSql.Append(" FROM tb_ReportTemplate ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return SQLHelper.ExecuteDataView(strSql.ToString());
        }
        #endregion  成员方法

        /// <summary>
        /// 返回某节点对应的模板名称
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        private string GetTemplateName(int uid, SqlConnection con, SqlTransaction trans)
        {
            string tmp = String.Empty;
            tmp = SQLHelper.ExecuteString("select FieldName+'|'+cast(FieldParent as varchar(10))+'|'+cast(RT_ID as varchar(10)) from tb_ReportTemplate where RT_ID=" + uid, con, trans);
            string[] ary = tmp.Split('|');
            if (ary[1] == "0")
                return ary[0];
            else
                return GetTemplateName(int.Parse(ary[1]), con, trans);
        }

        private string GetChildID(int uid, List<string> list, SqlTransaction trans)
        {
            string tmpID = String.Empty;
            DataView mydv = SQLHelper.ExecuteDataView("select RT_ID,FieldChildCount,FieldName  from tb_ReportTemplate where FieldParent =" + uid, SQLHelper.myconn, trans);
            for (int i = 0; i < mydv.Count; i++)
            {
                tmpID += mydv[i]["RT_ID"].ToString() + ",";
                if (mydv[i]["FieldChildCount"].ToString() == "0")
                    list.Add(mydv[i]["FieldName"].ToString());
                tmpID += GetChildID(int.Parse(mydv[i]["RT_ID"].ToString()), list, trans);
            }
            return tmpID;
        }

        public object GetBindList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select RT_ID,FieldName,FieldAlias,FieldType,FieldSelect,SelectContent,EecelCell,Remark,FieldParent,FieldChildCount,FieldNo,FieldFormula,FieldTemplate,cast(RT_ID as varchar(50))+'|'+FieldName as FieldInfo ");
            strSql.Append(" FROM tb_ReportTemplate ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by FieldNo");
            return SQLHelper.ExecuteDataView(strSql.ToString());    
        }
    }
}

